{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataBase\n",
    "\n",
    "#### What is database\n",
    "\n",
    "Database, also called electronic database, any collection of data, or information, that is specially organized for rapid search and retrieval by a computer. Databases are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations. \n",
    "\n",
    "#### Types of database\n",
    "\n",
    "In a relational database, digital information about a specific customer is organized into rows, columns and tables which are indexed to make it easier to find relevant information through SQL or NoSQL queries.\n",
    "\n",
    "**SQL**  Structured Query Language\n",
    "\n",
    "SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.\n",
    "\n",
    "**NoSQL** Not only SQL \n",
    "\n",
    "MongoDB is an open source NoSQL database management program. NoSQL is used as an alternative to traditional relational database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MySQL \n",
    "\n",
    "A SQL query is a question you ask the database. If any of the data in the database satisfies the conditions of your query, SQL retrieves that data.\n",
    "\n",
    ">A SQL query is a question you ask the database. If any of the data in the database satisfies the conditions of your query, SQL retrieves that data.\n",
    "\n",
    "`SELECT * FROM EMPLOYEE WHERE Age > 40 OR Salary > 100000`\n",
    "\n",
    "If in case MySQL is not available, you might use MariaDB instead. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Download\n",
    "\n",
    "MySQL  USTC 中科大的镜像 [点击前往](http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/)\n",
    "\n",
    "注意根据你的操作系统选择合适的镜像文件 ！\n",
    "\n",
    "MariaDB 开源软件 可以到其官网下载 \n",
    "\n",
    "https://downloads.mariadb.org/\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 安装MariaDB/MySQL\n",
    "\n",
    "\n",
    "Windows10系统下安装MariaDB 的教程图解 [Click](https://pcedu.pconline.com.cn/1367/13676123.html)\n",
    "\n",
    "Windows10 MYSQL Installer 安装 [Click](https://www.runoob.com/w3cnote/windows10-mysql-installer.html)\n",
    "\n",
    "注意：在安装过程中要设置root用户的密码，请记住并且妥善保管这个密码，在后续查看及调用数据库的时候都需要使用这个重要的密码。而且root用户是具有最高权限的，不能泄露该密码。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 如果需要修改密码，可以根据以下的方法\n",
    "\n",
    "Enter password：***  \n",
    "\n",
    "mysql>use mysql;   \n",
    "\n",
    "Database changed   \n",
    "\n",
    "mysql> update user set password=password(\"newPassword\") where user='root';  将root用户的密码修改为新的密码\n",
    "\n",
    "mysql> flush privileges; 刷新权限  \n",
    "\n",
    "mysql> quit; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Please provide the password: 123456\n",
      "Please input the name of the database which you would like to access: test\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "pw = input('Please provide the password:')\n",
    "db = input('Please input the name of the database which you would like to access:')\n",
    "engine = create_engine('mysql+pymysql://root:{}@127.0.0.1:3306/{}?charset=utf8'.format(pw,db))\n",
    "# 127.0.0.1 代表本机，是特指本机的IP地址，3306是数据库使用的端口号，utf8是编码，一般有中文就应该使用utf8."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "# Write to database\n",
    "dat = pd.read_csv('stockdata.csv')\n",
    "dat.to_sql(name='my_table', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>close</th>\n",
       "      <th>vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20200724</td>\n",
       "      <td>16.20</td>\n",
       "      <td>285487.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20200723</td>\n",
       "      <td>16.51</td>\n",
       "      <td>332693.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20200722</td>\n",
       "      <td>17.95</td>\n",
       "      <td>184201.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20200721</td>\n",
       "      <td>17.21</td>\n",
       "      <td>226621.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20200720</td>\n",
       "      <td>16.97</td>\n",
       "      <td>320102.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trade_date  close        vol\n",
       "0    20200724  16.20  285487.23\n",
       "1    20200723  16.51  332693.07\n",
       "2    20200722  17.95  184201.74\n",
       "3    20200721  17.21  226621.76\n",
       "4    20200720  16.97  320102.62"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_cmd = \"select trade_date, close, vol from my_table where vol > 100000\"\n",
    "\n",
    "# Read from Database\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Navicat\n",
    "\n",
    "Powerful database management & design GUI \n",
    "\n",
    "是一套多连接数据库开发工具，让你在单一应用程序中同时连接多达七种数据库：MySQL、MariaDB、MongoDB、SQL Server、SQLite、Oracle 和 PostgreSQL，可一次快速方便地访问所有数据库。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>ChgAmount</th>\n",
       "      <th>ChgRate</th>\n",
       "      <th>Volume</th>\n",
       "      <th>VolTrans</th>\n",
       "      <th>Swing</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-10</td>\n",
       "      <td>12.70</td>\n",
       "      <td>13.09</td>\n",
       "      <td>12.68</td>\n",
       "      <td>13.02</td>\n",
       "      <td>0.32</td>\n",
       "      <td>2.52</td>\n",
       "      <td>906800</td>\n",
       "      <td>117420</td>\n",
       "      <td>3.23</td>\n",
       "      <td>0.32</td>\n",
       "      <td>stock_600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-15</td>\n",
       "      <td>12.88</td>\n",
       "      <td>13.08</td>\n",
       "      <td>12.85</td>\n",
       "      <td>13.02</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.85</td>\n",
       "      <td>613207</td>\n",
       "      <td>79686</td>\n",
       "      <td>1.78</td>\n",
       "      <td>0.22</td>\n",
       "      <td>stock_600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-17</td>\n",
       "      <td>12.91</td>\n",
       "      <td>13.42</td>\n",
       "      <td>12.89</td>\n",
       "      <td>13.10</td>\n",
       "      <td>0.20</td>\n",
       "      <td>1.55</td>\n",
       "      <td>1271328</td>\n",
       "      <td>167524</td>\n",
       "      <td>4.11</td>\n",
       "      <td>0.45</td>\n",
       "      <td>stock_600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-18</td>\n",
       "      <td>13.14</td>\n",
       "      <td>13.25</td>\n",
       "      <td>13.05</td>\n",
       "      <td>13.24</td>\n",
       "      <td>0.14</td>\n",
       "      <td>1.07</td>\n",
       "      <td>1116081</td>\n",
       "      <td>146516</td>\n",
       "      <td>1.53</td>\n",
       "      <td>0.40</td>\n",
       "      <td>stock_600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-19</td>\n",
       "      <td>13.33</td>\n",
       "      <td>13.61</td>\n",
       "      <td>13.18</td>\n",
       "      <td>13.24</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1716489</td>\n",
       "      <td>230022</td>\n",
       "      <td>3.25</td>\n",
       "      <td>0.61</td>\n",
       "      <td>stock_600000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE   Open   High    Low  Close  ChgAmount  ChgRate   Volume  \\\n",
       "0  2018-01-10  12.70  13.09  12.68  13.02       0.32     2.52   906800   \n",
       "1  2018-01-15  12.88  13.08  12.85  13.02       0.11     0.85   613207   \n",
       "2  2018-01-17  12.91  13.42  12.89  13.10       0.20     1.55  1271328   \n",
       "3  2018-01-18  13.14  13.25  13.05  13.24       0.14     1.07  1116081   \n",
       "4  2018-01-19  13.33  13.61  13.18  13.24       0.00     0.00  1716489   \n",
       "\n",
       "   VolTrans  Swing  Turnover          CODE  \n",
       "0    117420   3.23      0.32  stock_600000  \n",
       "1     79686   1.78      0.22  stock_600000  \n",
       "2    167524   4.11      0.45  stock_600000  \n",
       "3    146516   1.53      0.40  stock_600000  \n",
       "4    230022   3.25      0.61  stock_600000  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/stock_data?charset=utf8')\n",
    "sql_cmd = \"select * from 2018to where code like '%%600000' and close > 13\"\n",
    "\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "# pip install mysql-connector\n",
    "\n",
    "\n",
    "conn = mysql.connector.connect(host= '127.0.0.1',port=3306,user=\"root\",\\\n",
    "                               password = '123456' ,database = 'news' ,charset=\"utf8\")\n",
    "cur = conn.cursor()\n",
    "sql = \"select * from sina_fin_news where rich_text like '%%浦发银行%%'\"\n",
    "cur.execute(sql)\n",
    "\n",
    "result = cur.fetchall()\n",
    "res = [r[4] for r in result]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['标普将浦发银行的评级展望由稳定降至负面。',\n",
       " '一财网援引上海悦合置业未具名代表的话报道称，因为资金链断裂，该公司的上海嘉定悦合国际广场项目已于6月1日停工。总建筑面积达22万平方米的项目已被当地法院查封。浦发银行通过电子银行回答彭博称，向悦合置业提供了超过2亿元贷款，已采取措施保全资产。 ',\n",
       " '浦发银行上半年净利润为227亿元人民币，同比增长16.9%，每股收益1.215元。',\n",
       " '浦发银行在上交所发布公告称，已获得银监会批准进行非公开发行优先股。',\n",
       " '上海黄金交易所国际板8家结算银行名单确定，分别是工商银行、农业银行、中国银行、建设银行、交通银行、浦发银行、兴业银行和民生银行。',\n",
       " '据新浪乐居，兴业银行、浦发银行已率先放开，执行认房不认贷，以家庭为单位，如果之前买过房但是贷款已还清，现在买房算首套房。兴业首套房贷利率最低9.5折。',\n",
       " '兴业银行向新浪财经否认将放松首套房认定标准，完全是失实报道；而平安银行和招商银行也表示未接到任何关于首套贷认定标准的通知。接近四大行人士向新浪财经透露称，银行无权出台认房不认贷认定标准。今日早些时候有报道称，兴业、浦发银行已率先放开，执行认房不认贷。',\n",
       " '9月23日，针对“兴业银行、浦发银行已率先放开，执行认房不认贷”的报道，兴业银行否认将放松首套房认定标准，称完全是失实报道；而平安银行和招商银行也表示未接到任何关于首套贷认定标准的通知。',\n",
       " '浦发银行：尽管央行和银监会鼓励银行业金融机构发行住房抵押贷款支持证券（MBS），但存量房贷证券化不会在近期大规模推出。不过，未来MBS或成为中国央行定向刺激的渠道。',\n",
       " '浦发银行称独立董事周勤业辞任。']"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 创建表格\n",
    "\n",
    "conn = mysql.connector.connect(host= '127.0.0.1',port=3306,user=\"root\",\\\n",
    "                               password = '123456' ,database = 'test' ,charset=\"utf8\")\n",
    "cur = conn.cursor()\n",
    "sql = \"create table if not exists stock_abc (id int not null, date varchar(20), open varchar(100))\"\n",
    "cur.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 删除表格\n",
    "\n",
    "sql = \"drop table stock_abc\"\n",
    "cur.execute(sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 写入表格\n",
    "conn = mysql.connector.connect(host= '127.0.0.1',port=3306,user=\"root\",\\\n",
    "                               password = '123456' ,database = 'test' ,charset=\"utf8\")\n",
    "cur = conn.cursor()\n",
    "sql = \"create table if not exists stock_abcd (id int not null unique auto_increment, date varchar(20), prices varchar(100), primary key(id))\"\n",
    "cur.execute(sql)\n",
    "sql =\"\"\"insert into stock_abcd (date, prices) values ('20200102', '12.3')\"\"\"\n",
    "try:\n",
    "    cur.execute(sql)\n",
    "    conn.commit()\n",
    "    \n",
    "except:\n",
    "    conn.rollback()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MongoDB\n",
    "\n",
    "`pip install pymongo`\n",
    " \n",
    "或者下载相应的wheel文件，tar.gz文件进行安装 \n",
    "\n",
    "- 安装wheel 先下载文件***.whl， 然后切换到该whl文件的目录。运行 pip install filename.whl \n",
    "- 安装tar.gz 先下载文件***.tar.gz, 然后将该文件解压到相应目录。运行 pip install 解压目录  注意该目录中应该有setup.py文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'interlock')"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymongo\n",
    " \n",
    "myclient = pymongo.MongoClient(\"mongodb://localhost:27017/\")\n",
    "mydb = myclient[\"interlock\"]\n",
    "\n",
    "mydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
